The project bugRzilla is an issue tracker for the R-core members. The motive to develop this project as many issues came under the Resolution section i.e. not properly documented, reported, spam, and so on. So to find efficiency, issues can be tracked down without much effort which can increase the production rate of R-Core members for solving high-quality issues like whose Severity is major, critical, and new contributors who want to contribute can start either with less Priority and Severity i.e. minor, normal.
To learn more, see bugRzilla.
The R_bugzilla data can be downloaded from link
To install SQL on Ubuntu one can refer a blog post by digitalocean. To install MySQL workbench on Ubuntu one can refer a blog post by linuxhint
Before one import the R_bugzilla SQL file one may need to create the (empty) database from MySQL if it doesn’t exist already and the exported SQL don’t contain CREATE DATABASE (exported with –no-create-db or -n option), before you can import it.
After considering this open your Terminal and run the command: mysql -u username -p database_name < file.sql
To connect bugRzilla SQL Database with R, I’ve used dplyr package, this will allow to interact with our data in the form of quarries.
# Accessing Tables names from the Database
src_dbi(con)
## src: mysql 8.0.25-0ubuntu0.20.04.1 [@localhost:/bugRzilla]
## tbls: attachments, bugs, bugs_activity, bugs_fulltext, bugs_mod, components,
## longdescs
# View the "bugs" tables from the bugRzilla database
bugs_db <- tbl(con, "bugs")
bugs_data <- bugs_db %>%
select(everything())
datatable(as.data.frame(bugs_data), options = list(scrollY = "645px"),
class = "display", fillContainer = T, width = NULL,
height = NULL, style = "default", editable = FALSE)
bugs <- bugs_db %>%
mutate_all(as.character) %>% # converting all columns data types to "char"
as_tibble %>% # must be a data frame for na_if to work
na_if("") %>% #replace empty strings with NA
# converting all "char" data types to int leaving the "char" datatype
mutate_at(vars(-one_of(c("bug_severity", "bug_status", "short_desc",
"priority", "rep_platform", "bug_file_loc",
"lastdiffed", "op_sys", "version",
"resolution", "target_milestone", "creation_ts",
"delta_ts", "status_whiteboard", "deadline"))), as.integer) %>%
# converting all columns data types to "Date" leaving the "char" and "int" datatype
mutate_at(vars(-one_of(c("bug_severity", "bug_status", "short_desc",
"bug_file_loc", "priority", "rep_platform",
"version", "resolution", "target_milestone",
"bug_id", "assigned_to", "op_sys",
"status_whiteboard", "product_id", "reporter",
"component_id", "qa_contact", "votes",
"everconfirmed", "reporter_accessible",
"cclist_accessible", "estimated_time",
"remaining_time"))), as.Date)
# removing null or empty columns
bugs <- bugs %>%
select(-c("target_milestone", "qa_contact", "status_whiteboard",
"remaining_time", "remaining_time", "estimated_time"))
bugs <- bugs %>%
select(everything())
datatable(as.data.frame(bugs), options = list(scrollY = "645px"),
class = "display", fillContainer = T, width = NULL,
height = NULL, style = "default", editable = FALSE)
# View the "attachments" Table from the database
attachments_db <- tbl(con, "attachments")
attachments_data <- attachments_db %>%
select(everything())
datatable(as.data.frame(attachments_data), options = list(scrollY = "565px"),
class = "display", fillContainer = T, width = NULL,
height =NULL, style = "default", editable = FALSE)
# Data Wrangling
attach_df <- attachments_db %>%
as_tibble %>% # must be a data frame for na_if to work
na_if("") %>% #replace empty strings with NA
# converting all columns data types to "Date" leaving the "char" and "int" datatype
mutate_at(vars(-one_of(c("attach_id", "bug_id", "description", "mimetype",
"ispatch", "filename", "submitter_id",
"isobsolete", "isprivate"))), as.Date)
attach_df <- attach_df %>%
select(-c("isprivate"))
attach_df <- attach_df %>%
select(everything())
datatable(as.data.frame(attach_df), options = list(scrollY = "565px"),
class = "display", fillContainer = T, width = NULL,
height = NULL, style = "default", editable = FALSE)
# View the "bugs_activity" Table from the database
bugs_activity_db <- tbl(con, "bugs_activity")
bugs_activity_data <- bugs_activity_db %>%
select(everything())
datatable(as.data.frame(bugs_activity_data), options = list(scrollY = "543px"),
class = "display", fillContainer = T, width = NULL,
height = NULL, style = "default", editable = FALSE)
# Data Wrangling
bugs_act <- bugs_activity_db %>%
as_tibble %>% # must be a data frame for na_if to work
na_if("") %>% #replace empty strings with NA
mutate_at(vars(-one_of(c("attach_id", "bug_id", "who", "fieldid", "added", "comment_id", "removed", "id"))), as.Date)
bugs_act <- bugs_act %>%
select(everything())
datatable(as.data.frame(bugs_act), options = list(scrollY = "543px"),
class = "display", fillContainer = T, width = NULL,
height = NULL, style = "default", editable = FALSE)